package cn.edu.lcu.cs.javaweb.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import cn.edu.lcu.cs.javaweb.entity.User;
import cn.edu.lcu.cs.javaweb.util.DBUtil;
import cn.edu.lcu.cs.javaweb.util.MyDataSource;

public class UserDaoJdbcImpl implements UserDao {

	private Connection getConnection() {
		return MyDataSource.getConnection();
	}

	@Override
	public User findById(Long id) {
		User user = null;
		try (Connection connection = getConnection();
				PreparedStatement statement = connection.prepareStatement("select * from user where id = ?")) {
			statement.setLong(1, id);
			ResultSet rs = statement.executeQuery();
			if (rs.next()) {
				user = dump(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}

	/**
	 * 从记录集中转储一条数据到一个JavaBean对象
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private User dump(ResultSet rs) throws SQLException {
		User user;
		user = new User();
		user.setId(rs.getLong("id"));
		user.setUsername(rs.getString("username"));
		Date birthdate = rs.getDate("birthdate");
		user.setBirthdate(birthdate != null ? birthdate.toLocalDate() : null);
		return user;
	}

	@Override
	public List<User> findAll() {
		List<User> users = new ArrayList<User>();
		try (Connection connection = getConnection();
				PreparedStatement statement = connection.prepareStatement("select * from user");
				ResultSet rs = statement.executeQuery()) {
			while (rs.next()) {
				// 将当前行中的数据转储到一个对象，并添加到列表
				users.add(dump(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public List<User> findByUsername(String username) {
		List<User> users = new ArrayList<User>();
		try (Connection connection = getConnection();
				PreparedStatement statement = connection.prepareStatement("select * from user where username = ?")) {
			statement.setString(1, username);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				// 将当前行中的数据转储到一个对象，并添加到列表
				users.add(dump(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public User authenticate(String username, String password) {
		User user = null;

		try (Connection connection = getConnection();
				PreparedStatement statement = connection
						.prepareStatement("select * from user where username=? and password=?")) {
			statement.setString(1, username);
			statement.setString(2, password);
			ResultSet rs = statement.executeQuery();
			if (rs.next()) {
				user = dump(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}

	@Override
	public User save(User user) {
		if (user != null) {
			// 根据情况，添加其它数据库的列
			// 若主键自动递增，则不需指定主键；否则，需要指定主键
			// String uuid = UUID.randomUUID().toString(); // 如果是字符串主键，最好是UUID，保证主键不重复
			// Statement.RETURN_GENERATED_KEYS 参数是为了获取数据自动生成的主键。如果不是自增主键，不需要此参数。
			try (Connection connection = getConnection();
					PreparedStatement statement = connection.prepareStatement(
							"insert into user(username, password) values(?, ?)", Statement.RETURN_GENERATED_KEYS)) {
				statement.setString(1, user.getUsername());
				statement.setString(2, user.getPassword());
				int count = statement.executeUpdate(); // 执行一次更新操作（插入也是一种更新），返回影响的记录条数
				ResultSet rs = statement.getGeneratedKeys();
				if (rs.next()) {
					user.setId(rs.getLong(1));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}

	@Override
	public int delete(Long id) {
		try (Connection connection = getConnection()) {
			String sql = "DELETE FROM `user` where id=?";
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setLong(1, id);
			return statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int update(User user) {
		try (Connection connection = DBUtil.getConnection();) {
			String sql = "UPDATE `user` set `username`=?, `password`=? where id=?";
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, user.getUsername());
			statement.setString(2, user.getPassword());
			statement.setLong(3, user.getId());
			return statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public long count() {
		try (Connection connection = DBUtil.getConnection();) {
			String sql = "select count(*) from user";
			PreparedStatement statement = connection.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

}
